package Model;

import java.sql.ResultSet;

import static Model.DataBase.insertEscape;
import static Model.DataBase.searchEscape;

/**
 * Created by FreyrWings on 2017/5/24.
 * Deal with the operation of table post
 */
public class PostDB {
    private static DataBase dataBase = new DataBase();

    public static void init(){

        String sql_init_seq =  "CREATE SEQUENCE post_id_incre start with 1 increment by 1";
        dataBase.executeStatement(sql_init_seq);

        String sql_init_table = "CREATE TABLE POST(\n" +
                "  id INTEGER PRIMARY KEY,\n" +
                "  title NVARCHAR2(50) NOT NULL,\n" +
                "  image NVARCHAR2(50),\n" +
                "  description NVARCHAR2(500),\n" +
                "  createtime date NOT NULL\n" +
                ")";
        dataBase.executeStatement(sql_init_table);

        String sql_init_trig = "CREATE OR REPLACE TRIGGER new_post_timestamp\n" +
                "  BEFORE INSERT\n" +
                "  ON post\n" +
                "  FOR EACH ROW\n" +
                "  BEGIN\n" +
                "    :NEW.id := post_id_incre.nextval;\n" +
                "    :NEW.createtime := sysdate;\n" +
                "  END;";
        dataBase.executeStatement(sql_init_trig);
    }


    public static void create(Post post){
        String sql_create_post = "call create_post('"
                    + insertEscape(post.getTitle())+"','"+
                    insertEscape(post.getImage())+"','"+
                    insertEscape(post.getDescription())+"',"+
                    post.getAccountid()+")";
        dataBase.executeStatement(sql_create_post);
    }

    public static void modify(Post post){
        String sql_modify_post = "UPDATE POST SET " +
                "title='" + insertEscape(post.getTitle())+"',"+
                (post.getImage()==null?"":"image='" + insertEscape(post.getImage())+"',")+
                "description='"+insertEscape(post.getDescription())+"' \n" +
                "WHERE ID=" + post.getId();

        dataBase.executeStatement(sql_modify_post);
    }

    public static int getTotal(String keywords){
        String search = "";
        if(!keywords.equals("")){
            search +=  "WHERE description like '%" + searchEscape(keywords) +
                    "%' ESCAPE 'a' OR title like '%" + searchEscape(keywords) +"%' ESCAPE 'a' ";
        }
        ResultSet rs = dataBase.retrieveData("select count(*) from post " + search);
        int total = 0;
        try {
            if(rs.next()){
                total = Integer.parseInt(rs.getString(1));
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return total;
    }

    public static int getPage(String keywords, int num){
        return (getTotal(keywords)+num-1)/num;
    }

    public static Post retrieve(int id){
        Post post = new Post();
        ResultSet rs = dataBase.retrieveData("select id,title,image,description,to_char(createtime,'yyyy-mm-dd') as createtime, accountid from post where id="+id);
        try {
            if (rs.next()) {
                post.setId(Integer.parseInt(rs.getString("id")));
                post.setTitle(rs.getString("title"));
                post.setImage(rs.getString("image"));
                post.setDescription(rs.getString("description"));
                post.setDate(rs.getString("createtime"));
                post.setAccountid(Integer.parseInt(rs.getString("accountid")));
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return post;
    }

    public static void delete(int id){
        String sql_delete = "call del_post(" + id + ")";
        dataBase.executeStatement(sql_delete);
    }

    public static Post[] retrieve(String keywords, int page, int num){
        Post[] posts = new Post[num];
        String search = "";
        if(!keywords.equals("")){
            search +=  "WHERE description like '%" + searchEscape(keywords) +
            "%' ESCAPE 'a' OR title like '%" + searchEscape(keywords) +"%' ESCAPE 'a' ";
        }
        String sql_retrieve = "SELECT id,title,image,description,to_char(createtime,'yyyy-mm-dd') as createtime, accountid FROM\n" +
                "(SELECT pos.*, ROWNUM rn\n" +
                "FROM (SELECT * FROM POST " + search +" ORDER BY id DESC " +
                ") pos\n" +
                "  WHERE ROWNUM<="+ page*num +")\n" +
                "WHERE rn >"+ (page-1)*num;
        ResultSet rs = dataBase.retrieveData(sql_retrieve);

        int i = 0;
        try {
            while (rs.next()) {
                posts[i] = new Post();
                posts[i].setId(Integer.parseInt(rs.getString("id")));
                posts[i].setTitle(rs.getString("title"));
                posts[i].setImage(rs.getString("image"));
                posts[i].setDate(rs.getString("createtime"));
                posts[i].setAccountid(Integer.parseInt(rs.getString("accountid")));
                String des = rs.getString("description");
                des = des.length()>30?des.substring(0, 30)+"...":des;
                posts[i].setDescription(des);
                i++;
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return posts;
    }
}
